package cn.seqdata.jdbc;

import java.io.IOException;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.function.Function;
import java.util.stream.IntStream;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.Validate;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import com.google.common.base.Joiner;

public class SqlTemplate extends JdbcDaoSupport {
	public final String tableName;
	public final String[] keycols;
	public final Object[] keyvals;

	private Function<Object, Object> converter;

	public SqlTemplate(String tableName, String[] keycols) {
		this.tableName = tableName;
		this.keycols = keycols;
		this.keyvals = new Object[keycols.length];

	}

	public SqlTemplate(String tableName, String[] keycols, Function<Object, Object> converter) {
		this(tableName, keycols);

		this.setConverter(converter);
	}

	public void setKeyvals(Object[] keyvals) {
		System.arraycopy(keyvals, 0, this.keyvals, 0, Math.min(this.keyvals.length, keyvals.length));
	}

	public Function<Object, Object> getConverter() {
		return ObjectUtils.defaultIfNull(converter, Function.identity());
	}

	public void setConverter(Function<Object, Object> converter) {
		this.converter = converter;
	}

	public void select(String[] valcols, RowCallbackHandler rch) {
		getJdbcTemplate().query(selectSql(valcols), keyvals, rch);
	}

	public void insert() {
		getJdbcTemplate().update(insertSql(), keyvals);
	}

	public void delete() {
		getJdbcTemplate().update(deleteSql(), keyvals);
	}

	public void update(String[] valcols, Object[] values) {
		getJdbcTemplate().update(updateSql(valcols), ps -> {
			for(int i = 0; i < values.length; ++i) {
				ps.setObject(i + 1, getConverter().apply(values[i]));
			}
			for(int i = 0; i < keyvals.length; ++i) {
				ps.setObject(values.length + i + 1, keyvals[i]);
			}
		});
	}

	public void upsert(String[] valcols, Object[] values) {
		if(!exists()) {
			insert();
		}
		update(valcols, values);
	}

	public boolean exists() {
		return getJdbcTemplate().queryForObject(countSql(), keyvals, Boolean.class);
	}

	public <T> void batch(String[] valcols, Function<T, Object[]> function, List<T> batch) {
		getJdbcTemplate().batchUpdate(batchSql(valcols), new BatchPreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				Object[] values = function.apply(batch.get(i));
				Validate.isTrue(valcols.length == values.length);

				for(int j = 0; j < keyvals.length; ++j) {
					ps.setObject(j + 1, keyvals[j]);
				}
				for(int j = 0; j < valcols.length; ++j) {
					ps.setObject(keyvals.length + j + 1, getConverter().apply(values[j]));
				}
			}

			@Override
			public int getBatchSize() {
				return batch.size();
			}
		});
	}

	private String countSql() {
		return String.format("SELECT COUNT(*) FROM (%s) t", selectSql(keycols));
	}

	private String selectSql(String[] valcols) {
		//需要避免没有所需查询的字段，当要查询的valcols为空时，查询keycols
		if(ArrayUtils.isEmpty(valcols))
			valcols = keycols;

		try {
			StringWriter writer = new StringWriter();
			PrintWriter ps = new PrintWriter(writer);

			ps.append("SELECT ");
			Joiner.on(',')
				.appendTo(ps, Arrays.asList(valcols));
			ps.append(" FROM ")
				.append(tableName)
				.append(" WHERE ");
			Joiner.on(" AND ")
				.appendTo(ps, Arrays.stream(keycols)
					.map(x -> x + "=?")
					.iterator());
			return writer.toString();
		} catch(IOException ex) {
			return null;
		}
	}

	private String insertSql() {
		try {
			StringWriter writer = new StringWriter();
			PrintWriter ps = new PrintWriter(writer);

			ps.append("INSERT INTO ")
				.append(tableName)
				.append(" (");
			Joiner.on(',')
				.appendTo(ps, keycols);
			ps.append(") VALUES (");
			Joiner.on(',')
				.appendTo(ps, Arrays.stream(keycols)
					.map(x -> "?")
					.iterator());
			ps.append(')');

			return writer.toString();
		} catch(IOException ex) {
			return null;
		}
	}

	private String deleteSql() {
		try {
			StringWriter writer = new StringWriter();
			PrintWriter ps = new PrintWriter(writer);

			ps.append("DELETE FROM ")
				.append(tableName)
				.append(" WHERE ");
			Joiner.on(" AND ")
				.appendTo(ps, Arrays.stream(keycols)
					.map(x -> x + "=?")
					.iterator());

			return writer.toString();
		} catch(IOException ex) {
			return null;
		}
	}

	private String updateSql(String[] valcols) {
		try {
			StringWriter writer = new StringWriter();
			PrintWriter ps = new PrintWriter(writer);

			ps.append("UPDATE ")
				.append(tableName)
				.append(" SET ");
			Joiner.on(',')
				.appendTo(ps, Arrays.stream(valcols)
					.map(x -> x + "=?")
					.iterator());
			ps.append(" WHERE ");
			Joiner.on(" AND ")
				.appendTo(ps, Arrays.stream(keycols)
					.map(x -> x + "=?")
					.iterator());

			return writer.toString();
		} catch(IOException ex) {
			return null;
		}
	}

	private String batchSql(String[] valcols) {
		try {
			StringWriter writer = new StringWriter();
			PrintWriter ps = new PrintWriter(writer);

			ps.append("INSERT INTO ")
				.append(tableName)
				.append(" (");
			Joiner.on(',')
				.appendTo(ps, keycols);
			IntStream.range(0, valcols.length)
				.forEach(i -> ps.append(',')
					.append(valcols[i]));
			ps.append(") VALUES (");
			Joiner.on(',')
				.appendTo(ps, Arrays.stream(keycols)
					.map(x -> "?")
					.iterator());
			IntStream.range(0, valcols.length)
				.forEach(i -> ps.append(',')
					.append('?'));
			ps.append(')');

			return writer.toString();
		} catch(IOException ex) {
			return null;
		}
	}
}
